﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CinemaBookingProject.DBLayer;

public class DBSeat : DataBase
{
    //public override Object SingleWhere(int id)
    //{
    //    SqlDataReader rdr = null;
    //    Seat seat = null;
    //    try
    //    {
    //        Connection.Close();
    //        SqlCommand cmd = new SqlCommand("SELECT Seat from Seat where id = " + id, Connection);
    //        rdr = cmd.ExecuteReader();

    //        //Der er max et objekt med det givne ID.
    //        while(rdr.Read())
    //        {
    //            seat = new Seat((int)rdr["Row-No"], (int)rdr["SeatNo"], (int)rdr["CinemaRoom"]);
    //        }
    //    }
    //    finally
    //    {
    //        if (rdr != null)
    //            rdr.Close();
    //        if (Connection != null)
    //            Connection.Close();
    //    }

    //    return seat;
    //}

    //public static List<Seat> MiscWhere()
    //{
    //    SqlDataReader rdr = null;
    //    List<Seat> seat = null;
    //    try
    //    {
    //        Connection.Close();
    //        SqlCommand cmd = new SqlCommand("SELECT Seat from Seat where id = " + id, Connection);
    //        rdr = cmd.ExecuteReader();

    //        while (rdr.Read())
    //        {
    //            seat.Add(new Seat((int)rdr["Row-No"], (int)rdr["SeatNo"], (int)rdr["CinemaRoom"]));
    //        }
    //    }
    //    finally
    //    {
    //        if (rdr != null)
    //            rdr.Close();
    //        if (Connection != null)
    //            Connection.Close();
    //    }

    //    return seat;
    //}

    //public virtual SQLConnection con
    //{
    //    get;
    //    set;
    //}

    //public virtual Seat seat
    //{
    //    get;
    //    set;
    //}

    //public virtual Seat BuildSeat(DataRow dr)
    //{
    //    throw new System.NotImplementedException();
    //}



    //public virtual Seat SingleWhere(string query, SQLParameter[] parameter = null)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public virtual List<Seat> MiscWhere(string query, SQLParameter[] parameter = null)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public virtual Seat GetSeatByNo(int no)
    //{
    //    throw new System.NotImplementedException();
    //}

    #region Required Methods:
    /// <summary>
    /// Returns a seat object from the database.
    /// </summary>
    /// <param name="id">The ID of the seat.</param>
    /// <returns>Seat object</returns>
    public Seat Read(int id)
    {
        SqlDataReader rdr = null;
        Seat seat = null;
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT Seat from Seat where id = " + id, Connection);
            rdr = cmd.ExecuteReader();

            //Der er max ét objekt med det givne ID.
            while (rdr.Read())
            {
                seat = new Seat((int)rdr["RowNo"], 
                                (int)rdr["SeatNo"],
                                (double)rdr["Price"], 
                                (bool)rdr["Handicap"], 
                                (bool)rdr["LegSpace"], 
                                (int)rdr["CinemaRoom"]);
                //seat = new Seat((int)rdr["Row-No"], (int)rdr["SeatNo"], (int)rdr["CinemaRoom"]);
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return seat;
    }

    public List<Seat> ReadAll(int room)
    {
        SqlDataReader rdr = null;
        List<Seat> seats = new List<Seat>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from Seat", Connection);
            rdr = cmd.ExecuteReader();

            //Der er max ét objekt med det givne ID.
            while (rdr.Read())
            {
                //Console.WriteLine(rdr["CinemaRoom"].GetType());
                seats.Add(new Seat((int)rdr["RowNo"],
                                (int)rdr["SeatNo"],
                                (double)rdr["Price"],
                                (bool)rdr["Handicap"],
                                (bool)rdr["LegSpace"],
                                (byte)rdr["CinemaRoom"]));
                //seat = new Seat((int)rdr["Row-No"], (int)rdr["SeatNo"], (int)rdr["CinemaRoom"]);
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return seats;
    } 
    /// <summary>
    /// Inserts a seat into the database.
    /// </summary>
    /// <param name="seat">The seat to insert.</param>
    /// <returns>-1 if sqlexception (seat already in db or wrong room), 0 if nothing happened and 1 if the seat was inserted correctly.</returns>
    public int Insert(Seat seat)
    {
        int wentWell = 0;
        string query =
            "INSERT INTO Seat (ID, RowNo, SeatNo, Handicap, LegSpace, Price, CinemaRoom) VALUES (@ID, @Row, @Seat, @Handicap, @Legspace, @Price, @Room)";
        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((seat.CinemaRoom * 10000) + (seat.Row * 100) + seat.SeatNr);
            cmd.Parameters.Add("@Row", SqlDbType.Int).Value = seat.Row;
            cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = seat.SeatNr;
            cmd.Parameters.Add("@Handicap", SqlDbType.Bit).Value = seat.Handicap;
            cmd.Parameters.Add("@Legspace", SqlDbType.Bit).Value = seat.LegSpace;
            cmd.Parameters.Add("@Price", SqlDbType.Float).Value = seat.Price;
            cmd.Parameters.Add("@Room", SqlDbType.TinyInt).Value = seat.CinemaRoom;

            Connection.Open();
            wentWell = cmd.ExecuteNonQuery();
            
        }
        //looks like it already exists in the database!
        catch(SqlException)
        {
            wentWell = -1;
        }
        catch (Exception)
        { 
            throw;
        }
        finally
        {
            Connection.Close();
        }
        return wentWell;
    }

    
    public int Update(Seat seat)
    {
        int wentWell = 0;
        string query =
            "UPDATE Seat Handicap = @Handicap, LegSpace = @Legspace, Price = @Price WHERE ID = @ID";
        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((seat.CinemaRoom * 10000) + (seat.Row * 100) + seat.SeatNr);
            cmd.Parameters.Add("@Handicap", SqlDbType.Bit).Value = seat.Handicap;
            cmd.Parameters.Add("@Legspace", SqlDbType.Bit).Value = seat.LegSpace;
            cmd.Parameters.Add("@Price", SqlDbType.Float).Value = seat.Price;

            Connection.Open();
            wentWell = cmd.ExecuteNonQuery();
             
        }
        //looks like it already exists in the database!
        catch (SqlException)
        {
            wentWell = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            Connection.Close();
        }
        return wentWell;
    }

    public int Delete(Seat seat)
    {
        int wentWell = 0;
        string query =
            "DELETE FROM Seat WHERE ID = @ID";
        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((seat.CinemaRoom * 10000) + (seat.Row * 100) + seat.SeatNr);
            
            Connection.Open();
            wentWell = cmd.ExecuteNonQuery();

        }
        //looks like it already exists in the database!
        catch (SqlException)
        {
            wentWell = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            Connection.Close();
        }
        return wentWell;
    }
    #endregion
}

